package com.supermap.dataservice.cloud.loginlog;


import com.supermap.dataservice.util.Table;
import com.supermap.dubbo.api.cloud.loginlog.loginlog;
import javafx.scene.control.Tab;
import org.apache.ibatis.annotations.*;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 登录日志入库
 * @date 2018年12月04 上午 10:10
 * @email 1301584033@qq.com
 */
@Mapper
public interface LoginLogMapper {

    /***
     * 系统登陆日志sms
     * **/
    @SelectProvider(type = LoginLogMapperSQL.class, method = "sysLoginLog")
    List<loginlog> getSysLoginLog(@Param("loginlog") loginlog loginlog, @Param("table")Table table);

    /***
     * 获取登陆总条数
     * **/
    @SelectProvider(type = LoginLogMapperSQL.class,method = "sysLoginLogCount")
    int getSysLoginLogCount(@Param("loginlog") loginlog loginlog);
    /***
     * 入库
     * */
    @InsertProvider(type = LoginLogMapperSQL.class, method = "insertLoginLogSQL")
    int insertLoginLog(@Param("loginlog") loginlog loginlog);


    /**
     * 指定区域和时间范围内用户最近一次登录查询(含登录总次数)
     */
    @SelectProvider(type = LoginLogMapperSQL.class, method = "getLoginLogByUserIdAndareaCodeOrdTimeSQL")
    List<Map<String, Object>> getLoginLogByUserIdAndareaCodeOrdTime(@Param("areaCode") String areaCode, @Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @SelectProvider(type = LoginLogMapperSQL.class, method = "getLoginLogByUserIdAndareaCodeOrdTimeCountSQL")
    int getLoginLogByUserIdAndareaCodeOrdTimeCount(@Param("areaCode") String areaCode, @Param("beginTime") String beginTime, @Param("endTime") String endTime);


    /**
     * 指定用户和时间范围内所有登录日志查询
     */
    @SelectProvider(type = LoginLogMapperSQL.class, method = "getPageAllLoginLogByUserIdorTimeSQL")
    List<Map<String, Object>> getPageAllLoginLogByUserIdorTime(@Param("userId") String userId, @Param("beginTime") String beginTime, @Param("endTime") String endTime, @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @SelectProvider(type = LoginLogMapperSQL.class, method = "getPageAllLoginLogByUserIdorTimeCountSQL")
    int getPageAllLoginLogByUserIdorTimeCount(@Param("userId") String userId, @Param("beginTime") String beginTime, @Param("endTime") String endTime);

    class LoginLogMapperSQL {
        /**
         * 指定区域和时间范围内用户最近一次登录查询(含登录总次数)
         */
        public String getLoginLogByUserIdAndareaCodeOrdTimeSQL(Map<String, Object> map) {
            String areaCode = (String) map.get("areaCode");
            String beginTime = (String) map.get("beginTime");
            String endTime = (String) map.get("endTime");
            Integer pageNum = (Integer) map.get("pageNum");
            Integer pageSize = (Integer) map.get("pageSize");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t1.id,t1.userid,t1.hostName,t1.mac,t1.ip,t1.logintype,t1.lon,t1.lat,t1.logintime,(select count(id) from t_login_log where userid=t1.userid) as countSum  from t_login_log t1\n" +
                    "inner join (select id from t_user ");
            if (areaCode != null) {
                stringBuilder.append(" where areaCode like concat(" + areaCode + ",'%') ");
            }
            stringBuilder.append(") t2 on t1.userid=t2.id\n");

            if (beginTime != null && endTime != null) {
                stringBuilder.append("and t1.logintime between '" + beginTime + "' and '" + endTime + "'");
            }
            stringBuilder.append(" group by t1.userid\n" +
                    "order by  t1.logintime desc\n" +
                    "limit " + pageNum + "," + pageSize + " ");
            return stringBuilder.toString();
        }

        public String getLoginLogByUserIdAndareaCodeOrdTimeCountSQL(Map<String, Object> map) {
            String areaCode = (String) map.get("areaCode");
            String beginTime = (String) map.get("beginTime");
            String endTime = (String) map.get("endTime");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select count(1)  from (select t1.id from t_login_log t1\n" +
                    "inner join (select id from t_user ");
            if (areaCode != null) {
                stringBuilder.append(" where areaCode like concat(" + areaCode + ",'%') ");
            }
            stringBuilder.append(") t2 on t1.userid=t2.id ");

            if (beginTime != null && endTime != null) {
                stringBuilder.append("and t1.logintime between '" + beginTime + "' and '" + endTime + "'");
            }
            stringBuilder.append("   group by t1.userid " +
                    "order by  t1.logintime desc) a ");
            return stringBuilder.toString();
        }

        /**
         * 指定用户和时间范围内所有登录日志查询
         */
        public String getPageAllLoginLogByUserIdorTimeSQL(Map<String, Object> map) {
            String userId = (String) map.get("userId");
            String beginTime = (String) map.get("beginTime");
            String endTime = (String) map.get("endTime");
            Integer pageNum = (Integer) map.get("pageNum");
            Integer pageSize = (Integer) map.get("pageSize");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select t1.id,t1.userid,t1.hostName,t1.mac,t1.ip,t1.logintype,t1.lon,t1.lat,t1.logintime from t_login_log t1\n" +
                    "where  1=1 ");
            if (beginTime != null && endTime != null) {
                stringBuilder.append("and t1.logintime between '" + beginTime + "' and '" + endTime + "'");
            }
            if (userId != null) {
                stringBuilder.append(" and  t1.userid =" + userId + " ");
            }
            stringBuilder.append("" +
                    "order by  t1.logintime desc " +
                    "limit " + pageNum + "," + pageSize + " ");

            return stringBuilder.toString();
        }

        public String getPageAllLoginLogByUserIdorTimeCountSQL(Map<String, Object> map) {
            String userId = (String) map.get("userId");
            String beginTime = (String) map.get("beginTime");
            String endTime = (String) map.get("endTime");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("select count(1) from (select t1.id from t_login_log t1 " +
                    "where  1=1 ");
            if (beginTime != null && endTime != null) {
                stringBuilder.append("and t1.logintime between '" + beginTime + "' and '" + endTime + "'");
            }
            if (userId != null) {
                stringBuilder.append(" and  t1.userid =" + userId + " ");
            }
            stringBuilder.append(" " +
                    "order by  t1.logintime desc) a  ");

            return stringBuilder.toString();
        }

        /**
         * 入库
         */
        public String insertLoginLogSQL(Map<String, Object> map) {
            loginlog loginlog = (loginlog) map.get("loginlog");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("insert into t_login_log ");
            StringBuilder title = new StringBuilder();
            StringBuilder val = new StringBuilder();
            if (loginlog.getUserid() != 0) {
                title.append("userid,");
                val.append("" + loginlog.getUserid() + ",");
            }
            if (loginlog.getHostName() != null) {
                title.append("hostName,");
                val.append("'" + loginlog.getHostName() + "',");
            }
            if (loginlog.getMac() != null) {
                title.append("mac,");
                val.append("'" + loginlog.getMac() + "',");
            }
            if (loginlog.getIp() != null) {
                title.append("ip,");
                val.append("'" + loginlog.getIp() + "',");
            }

            if (loginlog.getLogintype() != 0) {
                title.append("logintype,");
                val.append("" + loginlog.getLogintype() + ",");
            }
            if (loginlog.getLon() != null) {
                title.append("lon,");
                val.append("" + loginlog.getLon() + ",");
            }
            if (loginlog.getLat() != null) {
                title.append("lat,");
                val.append("" + loginlog.getLat() + ",");
            }
            title.append("logintime,");
            val.append("'" + new SimpleDateFormat("yyyy-MM-dd:HH:mm:ss").format(new Date()) + "',");
            return stringBuilder.append("(" + title.substring(0, title.lastIndexOf(",")) + ") values(" + val.substring(0, val.lastIndexOf(",")) + ")").toString();
        }

        /**
         * 获取所有用户的登陆日志信息
         **/
        public String sysLoginLog(Map<String, Object> map) {
            //获取分页数据
            Table table = (Table) map.get("table");
            loginlog loginlog = (loginlog) map.get("loginlog");
            StringBuilder sql = new StringBuilder();
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            sql.append("SELECT\n" +
                    "\ta.userid,\n" +
                    "\ta.hostName,\n" +
                    "\ta.ip,\n" +
                    "\t(\n" +
                    "\t\tCASE a.logintype\n" +
                    "\t\tWHEN 1 THEN\n" +
                    "\t\t\t'安卓'\n" +
                    "\t\tWHEN 2 THEN\n" +
                    "\t\t\t'苹果'\n" +
                    "\t\tWHEN 3 THEN\n" +
                    "\t\t\t'网站'\n" +
                    "\t\tELSE\n" +
                    "\t\t\t'微信'\n" +
                    "\t\tEND\n" +
                    "\t) equipment,a.logintime,\n" +
                    "b.username userName,\n" +
                    "c.areaname areaName,\n" +
                    "d.roleName roleName");
            sql1.append(" FROM\n" +
                    "\t`t_login_log` a\n" +
                    "left JOIN t_user b ON a.userid = b.id\n" +
                    "LEFT JOIN t_area c ON b.areacode = c.areacode\n" +
                    "LEFT JOIN (\n" +
                    "\tSELECT\n" +
                    "\t\tt1.userid uid,\n" +
                    "\t\tt2. NAME roleName\n" +
                    "\tFROM\n" +
                    "\t\tt_user_role t1\n" +
                    "\tLEFT JOIN t_role t2 ON t1.roleid = t2.id\n" +
                    ") d ON d.uid = a.userid\n" +
                    "where 1=1 ");
            if(loginlog.getAreaCode()>0){
                sql1.append(" and c.areacode like '"+loginlog.getAreaCode()+"%'");
            }
            if (loginlog.getHostName() != null && !"".equals(loginlog.getHostName())) {
                sql1.append(" and a.hostName like '%" + loginlog.getHostName() + "%'");
            }
            if (loginlog.getStartTime() != null && !"".equals(loginlog.getStartTime())) {
                sql1.append(" and str_to_date(logintime,'%Y-%m-%d')>=DATE('" + loginlog.getStartTime() + "')");
            }
            if (loginlog.getEndTime() != null && !"".equals(loginlog.getEndTime())) {
                sql1.append(" and str_to_date(logintime,'%Y-%m-%d')<=DATE('" + loginlog.getEndTime() + "')");
            }
            if (loginlog.getUserid() >0) {
               sql1.append(" and a.userid='"+loginlog.getUserid()+"'");
            }else{
                sql.append(" ,count(a.userid) num ");
                sql1.append(" GROUP BY a.userid");
            }
            sql1.append(" ORDER BY a.logintime desc limit " + table.getStartPoint() + "," + table.getLimit()+";");
            return sql.toString()+sql1.toString();
        }
        /**
         * 获取所有用户的登陆日志信息总条数
         **/
        public String sysLoginLogCount(Map<String, Object> map) {
            //获取分页数据
            loginlog loginlog = (loginlog) map.get("loginlog");
            StringBuilder sql = new StringBuilder();
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            sql.append("select count(t1.uid) num from (SELECT a.userid uid ");
            sql1.append(" FROM\n" +
                    "\t`t_login_log` a\n" +
                    "left JOIN t_user b ON a.userid = b.id\n" +
                    "LEFT JOIN t_area c ON b.areacode = c.areacode\n" +
                    "LEFT JOIN (\n" +
                    "\tSELECT\n" +
                    "\t\tt1.userid uid,\n" +
                    "\t\tt2. NAME roleName\n" +
                    "\tFROM\n" +
                    "\t\tt_user_role t1\n" +
                    "\tLEFT JOIN t_role t2 ON t1.roleid = t2.id\n" +
                    ") d ON d.uid = a.userid\n" +
                    "where 1=1 ");
            if(loginlog.getAreaCode()>0){
                sql1.append(" and c.areacode like '"+loginlog.getAreaCode()+"%'");
            }
            if (loginlog.getHostName() != null && !"".equals(loginlog.getHostName())) {
                sql1.append(" and a.hostName like '%" + loginlog.getHostName() + "%'");
            }
            if (loginlog.getStartTime() != null && !"".equals(loginlog.getStartTime())) {
                sql1.append(" and str_to_date(logintime,'%Y-%m-%d')>=DATE('" + loginlog.getStartTime() + "')");
            }
            if (loginlog.getEndTime() != null && !"".equals(loginlog.getEndTime())) {
                sql1.append(" and str_to_date(logintime,'%Y-%m-%d')<=DATE('" + loginlog.getEndTime() + "')");
            }
            if (loginlog.getUserid() >0) {
                sql1.append(" and a.userid='"+loginlog.getUserid()+"'");
            }else{
                sql1.append(" GROUP BY a.userid");
            }
            sql1.append(" ORDER BY a.logintime desc ) t1;");
            return sql.toString()+sql1.toString();
        }
    }
}
